/*
 * Copyright (c) 2009 Bui Viet Thanh (thanhbv@gmail.com).
 *
 * This file is part of clicknlearn.
 *
 * clicknlearn is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * clicknlearn is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with clicknlearn.  If not, see <http://www.gnu.org/licenses/>.
 */
const {Cc,Ci} = require("chrome");
/**
 * Clicknlearn Data Access Object singleton.
 *
 * see: http://blog.anselmbradford.com/2009/04/21/object-oriented-javascript-tip-the-quintessential-singleton/
 * or http://kaijaeger.com/articles/the-singleton-design-pattern-in-javascript.html
 *
 * created and instantiated the class in one statemen:
 */
exports.CNL_DAO = new function(){
    /**
     * see: sqlite (storage in firefox) - https://developer.mozilla.org/en/Storage
     * & https://developer.mozilla.org/en/Code_snippets/File_I%2F%2FO
     */
// initialize:
    // ~/.mozilla/firefox/${profile}
    // ex: /home/thanhbv/.mozilla/firefox/08mo3aw2.dev
    var file = Cc["@mozilla.org/file/directory_service;1"]
            .getService(Ci.nsIProperties)
            .get("ProfD", Ci.nsIFile);
    file.append("vocabulary.sqlite");
    var dbHasBeenInit = file.exists();
    var storageService = Cc["@mozilla.org/storage/service;1"]
            .getService(Ci.mozIStorageService);
    this.dbConn = storageService.openDatabase(file); // Will also create the file if it does not exist
    if(! dbHasBeenInit)
        this.dbConn.executeSimpleSQL(
                "CREATE TABLE vocabulary (word VARCHAR PRIMARY KEY NOT NULL, originalUrl VARCHAR, " +
                "remind VARCHAR, read INTEGER NOT NULL DEFAULT 0)");//, lastModify DATETIME DEFAULT CURRENT_TIMESTAMP)");
    /**@param record CNLRec instance. Note: word is store in record.id*/
    this.create = function(record){
        var sql = "INSERT OR REPLACE INTO vocabulary (word, originalUrl, remind, read) VALUES ('"
                + record.id + "', '" + record.originalUrl + "', '"
                + record.remind + "', '" + record.read + "')";
        this.dbConn.executeSimpleSQL(sql);
    }
    this.update = function(record) {
        // look up the stored record with id = record.id, then set
        // its values to those of new record
        let sql = "UPDATE vocabulary SET originalUrl = :url, remind = :remind, read = :read" +
//                  ", lastModify = CURRENT_TIMESTAMP " +
                  "WHERE word = :word";
        let stm = this.dbConn.createStatement(sql);
        stm.params.url = record.originalUrl;
        stm.params.remind = record.remind;
        stm.params.read = record.read;
        stm.params.word = record.id;
        stm.execute();
    }
    this.remove = function(word) {
        // look up the stored record with id = record.id, then delete it.
        this.dbConn.executeSimpleSQL("DELETE FROM vocabulary WHERE word = '" + word + "'");
    }
    this.setWordRead = function(word){
        this.dbConn.executeSimpleSQL("UPDATE vocabulary SET read = 1 WHERE word = '" + word + "'");
    }
    this.getWordData = function(word){
        var statement = this.dbConn.createStatement(
                'SELECT remind, originalUrl, read FROM vocabulary WHERE word = :word');
        statement.params.word = word;
        if(statement.executeStep()){
            console.log("DAO.getWordData", statement.row);
            return statement.row;
        }
        return null;
//        return statement.executeStep()? statement.row : null;
    }
    this.selectUnreadWordsStm = function(){
        return this.dbConn.createStatement("SELECT word FROM vocabulary WHERE read = 0");
    }
}
